﻿using JYH.Business.WMS;
using JYH.Data;
using JYH.DataAccess;
using JYH.Model;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;

namespace JYH.Business
{
    public class SmartERP
    {
        
      

        /// <summary>
        /// 物料信息同步
        /// </summary>
        /// <param name="Date">日期</param>
        /// <param name="NO">料号</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public  bool SyncMaterialInfo(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;

            Sql sql = new Sql();
            List<BasMaterialInfo> ErpM = new List<BasMaterialInfo>();

            try
            {
                sql = new Sql();
                sql.Append(@"Select
                                   ima01 as MaterialNO,
                                   ima02 as MaterialName,
                                   ima021 as MaterialDesc,
                                   ima35 as WarehouseNO,
                                   ima36 as StorageNO,
                                   ima25 as UnitNO,
                                   ima109 as MaterialType,
                                   TO_CHAR(imadate, 'yyyy-MM-dd') as MaterialDate,
                                   ima23 as KeepUserID
                              from ima_file");
                if (!string.IsNullOrEmpty(NO))
                {
                    sql.Append("WHere ima_file.ima01='" + NO.Trim() + "'");
                }
                if (!string.IsNullOrEmpty(Date))
                {
                    sql.Append("WHere TO_CHAR(ima_file.imadate, 'yyyy-MM-dd')='" + Date + "'");
                }
                sql.OrderBy("TO_CHAR(imadate, 'yyyy-MM-dd') desc");
                var dbERP = DB.D("ERPCONN");
                ErpM = dbERP.Fetch<BasMaterialInfo>(sql.SQL);
                if (ErpM.Count > 0)
                {
                    return BasMaterialInfoBLL.SyncMaterialInfo(ErpM, out Msg);
                }
                
                return true;
            }
            catch (Exception e)
            {
                Msg = e.Message;

                return false;
            }
        }

        /// <summary>
        /// 仓库信息同步
        /// </summary>
        /// <param name="Date">日期</param>
        /// <param name="NO">料号</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public  bool SyncWarehouseInfo(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;

            Sql sql = new Sql();
            List<BasWarehouseInfo> ErpM = new List<BasWarehouseInfo>();

            try
            {
                sql = new Sql();
                sql.Append(@"select imd01 as WarehouseNO,
                                           imd02 as WarehouseName,
                                           TO_CHAR(imddate, 'yyyy-MM-dd') as CurrencyDate
                                     from imd_file");
                if (!string.IsNullOrEmpty(NO))
                {
                    sql.Append("WHere imd_file.imd01='" + NO + "'");
                }
                if (!string.IsNullOrEmpty(Date))
                {
                    sql.Append("WHere TO_CHAR(imd_file.imddate, 'yyyy-MM-dd')>='" + Date + "'");
                }
                sql.OrderBy(" TO_CHAR(imddate, 'yyyy-MM-dd') desc");
               Database dbERP = DB.D("ERPCONN");
                ErpM = dbERP.Fetch<BasWarehouseInfo>(sql.SQL);
                if (ErpM.Count > 0)
                {
                    return BasWarehouseInfoBLL.SyncWarehouseInfo(ErpM, out Msg);
                }

                return true;
            }
            catch (Exception e)
            {
                Msg = e.Message;

                return false;
            }
        }

        /// <summary>
        /// 计量单位信息同步
        /// </summary>
        /// <param name="Date">日期</param>
        /// <param name="NO">料号</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public  bool SyncUnitInfo(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;

            Sql sql = new Sql();
            List<BasUnitInfo> ErpM = new List<BasUnitInfo>();

            try
            {
                sql = new Sql();
                sql.Append(@"Select gfe01 as UnitNO,
                                       gfe02 as UnitName,
                                       TO_CHAR(gfedate, 'yyyy-MM-dd') as UnitDate
                                  from gfe_file");
                if (!string.IsNullOrEmpty(NO))
                {
                    sql.Append("WHere gfe_file.gfe01='" + NO + "'");
                }
                if (!string.IsNullOrEmpty(Date))
                {
                    sql.Append("WHere TO_CHAR(gfe_file.gfedate, 'yyyy-MM-dd')>='" + Date + "'");
                }
                sql.OrderBy(" TO_CHAR(gfe_file.gfedate, 'yyyy-MM-dd') desc");
                Database dbERP = DB.D("ERPCONN");
                ErpM = dbERP.Fetch<BasUnitInfo>(sql.SQL);
                if (ErpM.Count > 0)
                {
                    return BasUnitInfoBLL.SyncUnitInfo(ErpM, out Msg);
                }

                return true;
            }
            catch (Exception e)
            {
                Msg = e.Message;

                return false;
            }
        }

        /// <summary>
        /// 供应商信息同步
        /// </summary>
        /// <param name="Date">日期</param>
        /// <param name="NO">料号</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public  bool SyncSupplyInfo(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;

            Sql sql = new Sql();
            List<BasPartnerInfo> ErpM = new List<BasPartnerInfo>();

            try
            {
                sql = new Sql();
                sql.Append(@"select pmc01 as PartnerNO,
                                    pmc03 as PartnerCode,
								    PMC081 as PartnerName,
                                    '0' as RType,
                                    TO_CHAR(pmc_file.pmcdate, 'yyyy-MM-dd') as SupplyDate
											
                              from pmc_file");
                if (!string.IsNullOrEmpty(NO))
                {
                    sql.Append("WHere pmc_file.pmc01='" + NO + "'");
                }
                if (!string.IsNullOrEmpty(Date))
                {
                    sql.Append("WHere TO_CHAR(pmc_file.pmcdate, 'yyyy-MM-dd')>='" + Date + "'");
                }
                sql.OrderBy(" TO_CHAR(pmc_file.pmcdate, 'yyyy-MM-dd') desc");
                Database dbERP = DB.D("ERPCONN");
                ErpM = dbERP.Fetch<BasPartnerInfo>(sql.SQL);
                if (ErpM.Count > 0)
                {
                    return BasPartnerInfoBLL.SyncPartnerInfo(ErpM, out Msg);
                }

                return true;
            }
            catch (Exception e)
            {
                Msg = e.Message;

                return false;
            }
        }
        /// <summary>
        /// 客户信息同步
        /// </summary>
        /// <param name="Date">日期</param>
        /// <param name="NO">料号</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public  bool SyncCustomInfo(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;

            Sql sql = new Sql();
            List<BasPartnerInfo> ErpM = new List<BasPartnerInfo>();

            try
            {
                sql = new Sql();
                sql.Append(@"select occ01 as PartnerNO,
                                      occ18 as PartnerName,
                                       occ02 as PartnerCode,
									   '1' as RType,
                                       TO_CHAR(occdate, 'yyyy-MM-dd') as CustomDate			
                                  from occ_file");
                if (!string.IsNullOrEmpty(NO))
                {
                    sql.Append("WHere occ_file.occ01='" + NO + "'");
                }
                if (!string.IsNullOrEmpty(Date))
                {
                    sql.Append("WHere TO_CHAR(occ_file.occdate, 'yyyy-MM-dd')>='" + Date + "'");
                }
                sql.OrderBy(" TO_CHAR(occ_file.occdate, 'yyyy-MM-dd') desc");
                Database dbERP = DB.D("ERPCONN");
                ErpM = dbERP.Fetch<BasPartnerInfo>(sql.SQL);
                if (ErpM.Count > 0)
                {
                    return BasPartnerInfoBLL.SyncPartnerInfo(ErpM, out Msg);
                }

                return true;
            }
            catch (Exception e)
            {
                Msg = e.Message;

                return false;
            }
        }
        public  bool SyncReciveOrder(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;
          
            Sql sql = new Sql();
            List<WmsReciveItem> ErpM = new List<WmsReciveItem>();

            try
            {
                sql = new Sql();
                sql.Append(@"SELECT
	                            rvb_file.rvb01 AS ReciveOrderNO,
	                            rvb_file.rvb02 AS ERPItem,
	                            rvb_file.rvb05 AS MaterialNO,
	                            ima_file.ima02 AS MaterialName,
	                            ima_file.ima021 AS MaterialSpec,
	                            rvb_file.rvb08 AS ReciveQty,
	                            rvb_file.rvb08 AS OKQty,
	                            0  AS LabelQty,
	                            rvb_file.rvb36 AS WarehouseNO,
	                            rvb_file.rvb37 AS StorageNO,
	                            rvb_file.rvb03 AS POItem,
	                            rvb_file.rvb04 AS PONO,
	                            rvb_file.rvb38 AS BatchNO,
	                            ima_file.ima25 AS UnitNO,
	                            TO_CHAR( rva_file.rva06, 'yyyy-MM-dd' ) AS ReciveDate,
	                            rva_file.rva02 AS SourceOrderNO,
	                            rva_file.rva05 AS PartnerNO,
	                            rva_file.rva07 AS PatnerOrderNO
                            FROM
	                            rvb_file
	                            INNER JOIN ima_file ON rvb_file.rvb05 = ima_file.ima01
	                            INNER JOIN rva_file ON rvb_file.rvb01 = rva_file.Rva01
	                            INNER JOIN IMG_FILE ON IMG_FILE.img01 = rvb_file.rvb05 
	                            AND rvb_file.rvb36 = IMG_FILE.IMG02 
	                            AND rvb_file.rvb37 = IMG_FILE.IMG03 
	                            AND rvb_file.rvb38 = IMG_FILE.IMG04");

                if (string.IsNullOrEmpty(Date) && string.IsNullOrEmpty(NO))
                {
                    Date = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(NO))
                {
                    sql.Append("WHere  rvb_file.rvb01='" + NO + "'");
                }
                if (!string.IsNullOrEmpty(Date))
                {
                    sql.Append("WHere  TO_CHAR(rva_file.RVADATE, 'yyyy-MM-dd')='" + Date + "'");
                }
                sql.OrderBy("rvb_file.rvb01,rvb_file.rvb02");
                Database dbERP = DB.D("ERPCONN");
                ErpM = dbERP.Fetch<WmsReciveItem>(sql.SQL);
                if (ErpM.Count > 0)
                {
                    List<WmsReciveOrderInfo> Orders = new List<WmsReciveOrderInfo>();
                    foreach (var item in ErpM)
                    {
                     int index=Orders.FindIndex(p=>p.ReciveOrderNO == item.ReciveOrderNO);
                        if ((index<0))
                        {
                            WmsReciveOrderInfo info = new WmsReciveOrderInfo();
                            info.ERPNO = item.ReciveOrderNO;
                            info.ReciveOrderNO = item.ReciveOrderNO;
                            info.WarehouseNO = item.WarehouseNO;
                            info.PartnerNO = item.PartnerNO;
                            info.PatnerOrderNO = item.PatnerOrderNO;
                            info.ReciveOrderNO = item.ReciveOrderNO;
                            info.ReciveDate = item.ReciveDate;
                            info.Items = new List<WmsReciveItem>();
                            WmsReciveItem obj = new WmsReciveItem();
                            obj.ReciveOrderNO = item.ReciveOrderNO;
                            obj.ERPItem = item.ERPItem;
                            obj.MaterialNO = item.MaterialNO;
                            obj.ReciveQty = item.ReciveQty;
                            obj.LabelQty = item.ReciveQty;
                            obj.PONO = item.PONO;
                            obj.POItem = item.POItem;
                            obj.OKQty = item.ReciveQty;
                            obj.BatchNO = item.BatchNO;
                            obj.WarehouseNO = item.WarehouseNO;
                            info.Items.Add(obj);
                            Orders.Add(info);
                        }
                        else
                        {
                            WmsReciveItem obj = new WmsReciveItem();
                            obj.ReciveOrderNO = item.ReciveOrderNO;
                            obj.ERPItem = item.ERPItem;
                            obj.MaterialNO = item.MaterialNO;
                            obj.ReciveQty = item.ReciveQty;
                            obj.BatchNO = item.BatchNO;
                            obj.WarehouseNO = item.WarehouseNO;
                            obj.ReciveQty = item.ReciveQty;
                            obj.LabelQty = item.ReciveQty;
                            obj.OKQty = item.ReciveQty;
                            obj.PONO = item.PONO;
                            obj.POItem = item.POItem;
                            Orders[index].Items.Add(obj);
                        }

                    }



                    return WmsReciveOrderInfoBLL.SyncReciveOrder(Orders, out Msg);
                }





  



                return true;
            }
            catch (Exception e)
            {
                Msg = e.Message;

                return false;
            }
        }

        /// <summary>
        /// 生产领料单同步
        /// </summary>
        /// <param name="Date"></param>
        /// <param name="NO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool SyncProductionPickingOrder(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;
            List<WmsTradeOrderInfo> Infos = new List<WmsTradeOrderInfo>();
            Database db = DB.D("ERPCONN");
            Sql sql = new Sql();
            try
            {

                sql = new Sql();
                sql.Append(@"Select 
                                    sfp_file.sfp01 AS TradeOrderNO,
                                    to_char(sfp_file.sfp02,'yyyy-MM-dd') AS TradeDate,
                                    '3' AS TradeType,
                                       
                                    sfp_file.*
                                    From sfp_file");

                Sql whereSql = new Sql();
                if (string.IsNullOrEmpty(NO) && string.IsNullOrEmpty(Date))
                {
                    Date = DateTime.Now.ToString("yyyy-MM-dd");
                }
                whereSql.Append(" Where sfp_file.sfp06 in (1,2,3,4)");
                if (!string.IsNullOrEmpty(NO))
                {
                    //whereSql.Append("Where  sfp_file.sfp01='" + OrderNO + "'");
                    whereSql.Append(" And  sfp_file.sfp01='" + NO + "' ");

                }
   
                    if (!string.IsNullOrEmpty(Date))
                    {
                        whereSql.Append(" And TO_CHAR(sfp_file.sfp02, 'yyyy-MM-dd')='" + Date.Replace('/','-') + "'");

                    }
             
                sql.Append(whereSql);
                sql.OrderBy("sfp_file.sfp01");
         
                

                Infos =db.Fetch<WmsTradeOrderInfo>(sql.SQL);
                sql = new Sql();
                sql.Append(@"Select sfs_file.sfs01 AS TradeOrderNO,
                                    sfs_file.sfs02 AS  ErpItem,
                                    sfs_file.sfs03  AS SourceOrderNO,
                                    sfs_file.sfs04  AS MaterialNO,
                                    sfs_file.sfs06  AS  UnitNO,
                                    sfs_file.sfs05  AS RequestQty,
                                    sfs_file.sfs07  AS WarehouseNO,
                                    sfs_file.sfs08  AS StorageNO,
                                    sfs_file.sfs09  AS BatchNO,
                                    sfp_file.sfpdate,
                                    sfs_file.*
                                    From sfs_file
                                    Inner Join sfp_file On sfs_file.sfs01=sfp_file.sfp01");
                sql.Append(whereSql);
                sql.OrderBy(" sfs_file.sfs01, sfs_file.sfs02");
                List<WmsTradeSource> Sources=db.Fetch<WmsTradeSource>(sql.SQL);
                List<WmsTradeOrderInfo> News = new List<WmsTradeOrderInfo>();
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    WmsTradeOrderInfo obj = new WmsTradeOrderInfo();
                    obj.TradeOrderNO = info.TradeOrderNO;
                    obj.TradeDate= info.TradeDate;
                    obj.TradeType = 3;
                    obj.TradeSources = new List<WmsTradeSource>();
                    List<WmsTradeSource> Items=Sources.FindAll(p=>p.TradeOrderNO == info.TradeOrderNO);
                    foreach (WmsTradeSource item in Items)
                    {
                        obj.TradeSources.Add(item);
                        if (!string.IsNullOrEmpty(item.WarehouseNO)&&string.IsNullOrEmpty(obj.FromWarehouseNO))
                        {
                            obj.FromWarehouseNO = item.WarehouseNO;
                        }
                     
                    }
                    News.Add(obj);
                }
                if (News.Count > 0)
                {
                    return WmsProductionPickingOrderBLL.SyncProductionPickingOrder(News, out Msg);
                }
                else
                {
                    Msg = "NO Data";
                    return false;
                }
             
            }
            catch (Exception ex)
            { 
                Msg=ex.ToString();
                return false;
            }
        }

        /// <summary>
        /// 生产领料单回抛
        /// </summary>
        /// <param name="Order">领料单对象</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public bool BackProductionPickingOrder(string TradeOrderNO , out string Msg)
        { 
            Msg=string.Empty;
            Database db = DB.D("ERPCONN");
            Sql sql = new Sql();
            StringBuilder sb = new StringBuilder();
            try
            {
                WmsTradeOrderInfo Order = WmsProductionPickingOrderBLL.GetProductionPickingOrder(TradeOrderNO, out Msg);
                if (Order == null)
                {
                    return false;
                }
                sql = new Sql();
                sql.Append(@"Select 
                                    sfp_file.sfp01 AS TradeOrderNO,
                                    sfp_file.sfp04 AS RState
                                    From sfp_file");
                sql.Append(" Where  sfp_file.sfp01='" + Order.TradeOrderNO + "' ");
                WmsTradeOrderInfo ERP = db.FirstOrDefault<WmsTradeOrderInfo>(sql);
                if (ERP == null)
                {
                    Msg = "NO Order";
                    return false;
                }
                if (!string.Equals( ERP.RState,  "N"))
                {
                    Msg = "Check  ERP Order Statu";
                    return false;
                }
                string abc = @"     Update sfs_file Set sfs07='{0}',sfs08='{1}',sfs05={2',sfs09='{3}',  sfs04='{4}'                         
                                     Where sfs01 = '{5}' And sfs02 ='{6}'";
                if (Order.TradeSources == null || Order.TradeSources.Count < 1)
                {
                    Msg = "NO Detail Data";
                    return false;
                }
                if (Order.TradeSources.Count >= 1)
                {
                    sb.Append(" BEGIN ");
                    for (int i = 0; i < Order.TradeSources.Count; i++)
                    {
                        sb.Append(string.Format(abc, Order.TradeSources[i].WarehouseNO, Order.TradeSources[i].StorageNO, Order.TradeSources[i].ActionQty, Order.TradeSources[i].BatchNO, Order.TradeSources[i].MaterialNO, Order.TradeSources[i].TradeOrderNO, Order.TradeSources[i].ErpItem) + ";");
                    }
                    sb.Append("  END ;");
                }
         

              
    
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }

                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }

        }


        /// <summary>
        /// 采购入库单同步
        /// </summary>
        /// <param name="Date"></param>
        /// <param name="NO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool SyncPurchaseReceiptOrder(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;
            List<WmsTradeOrderInfo> Infos = new List<WmsTradeOrderInfo>();
            Database db = DB.D("ERPCONN");
            Sql sql = new Sql();
            try
            {

                sql = new Sql();
                sql.Append(@"Select 
                                    rvu_file.rvu01 AS TradeOrderNO,
                                    rvu_file.rvu02  AS  SourceOrderNO,
                                    to_char(rvu_file.rvu03,'yyyy-MM-dd') AS  TradeDate,
                                    rvu_file.rvu04  AS  SupplyNO,
                                    '0' as TradeType,
                                    '0' AS RecvType,
                                    to_char(rvu_file.rvudate,'yyyy-MM-dd')  AS  UpdateDate
                                    From rvu_file");

                Sql whereSql = new Sql();
                if (string.IsNullOrEmpty(Date) && string.IsNullOrEmpty(Date))
                {
                    Date = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(NO))
                {
                    whereSql.Append("Where  rvu_file.rvu01='" + NO + "'");

                }

                if (!string.IsNullOrEmpty(Date))
                {
                    whereSql.Append("Where TO_CHAR(rvu_file.rvudate, 'yyyy-MM-dd')='" + Date + "'");
                 
                }
                whereSql.Append("Where rvu_file.rvu00=1");
                sql.Append(whereSql);
                sql.OrderBy("rvu_file.rvu01");
                Infos = db.Fetch<WmsTradeOrderInfo>(sql.SQL);
                sql = new Sql();
                sql.Append(@";select rvv_file.rvv01 AS TradeOrderNO,
                                       rvv_file.rvv04  AS  SourceOrderNO,
                                       rvv_file.rvv05  AS  SourceOrderItem,
                                       rvv_file.rvv31  AS  MaterialNO,
                                       rvv_file.rvv32  AS  FromWarehouseNO,
                                       rvv_file.rvv34  AS BatchNO,
                                       rvv_file.rvv17  AS  RequestQty,
                                       rvv_file.rvv02  AS  ErpItem,
                                       0  AS  ActionQty,
                                       rvv_file.rvv35  AS  UnitNO,
                                       rvv_file.*
                                 from rvv_file
                                 Inner Join rvu_file on rvu_file.rvu01=rvv_file.rvv01");
                sql.Append(whereSql);
                sql.OrderBy(" rvv_file.rvv01,rvv_file.rvv02");
                List<WmsTradeSource> Sources = db.Fetch<WmsTradeSource>(sql);
                List<WmsTradeOrderInfo> News = new List<WmsTradeOrderInfo>();
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    WmsTradeOrderInfo obj = new WmsTradeOrderInfo();
                    obj.TradeOrderNO = info.TradeOrderNO;
                    obj.SourceOrderNO = info.SourceOrderNO;
                    obj.TradeDate = info.TradeDate;
                    obj.TradeType = 0;
                    obj.TradeSources = new List<WmsTradeSource>();
                    List<WmsTradeSource> Items = Sources.FindAll(p => p.TradeOrderNO == info.TradeOrderNO);
                    foreach (WmsTradeSource item in Items)
                    {
                        obj.TradeSources.Add(item);
                    }
                    News.Add(obj);
                }

                return WmsPurchaseReceiptOrderBLL.SyncPurchaseReceiptOrder(News, out Msg);
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
        }



        /// <summary>
        /// 调拨单同步
        /// </summary>
        /// <param name="Date"></param>
        /// <param name="NO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool SyncStockTransferOrder(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;
            List<WmsTradeOrderInfo> Infos = new List<WmsTradeOrderInfo>();
            Database db = DB.D("ERPCONN");
            Sql sql = new Sql();
            try
            {

                sql = new Sql();
                sql.Append(@";Select imm_file.imm01  TradeOrderNO,to_char(imm_file.imm02,'yyyy-MM-dd') TradeDate,
                                        imm_file.imm09 Remark, 
                                        imm_file.imm14 SourceOrderNO,
                                        '5' TradeType,
                                        to_char(imm_file.immdate,'yyyy-MM-dd') UpdateDate,
                                        imm_file.*   From imm_file");

                Sql whereSql = new Sql();
                if (string.IsNullOrEmpty(Date) && string.IsNullOrEmpty(NO))
                {
                    Date = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(NO))
                {
                    //whereSql.Append("Where  sfp_file.sfp01='" + OrderNO + "'");
                    whereSql.Append("Where imm_file.imm01='" + NO + "'");

                }

                if (!string.IsNullOrEmpty(Date))
                {
                    whereSql.Append("WHere TO_CHAR(imm_file.immdate, 'yyyy-MM-dd')='" + Date + "'");

                  
                }
     
                sql.Append(whereSql);
                sql.OrderBy("imm_file.imm01");
                Infos = db.Fetch<WmsTradeOrderInfo>(sql.SQL);
                sql = new Sql();
                sql.Append(@";Select imn01 AS  TradeOrderNO,
                                     imn02  AS   ErpItem,
                                     imn03  AS   MaterialNO,
                                     imn04  AS  FromWarehouseNO,
                                     imn05  AS   FromStorageNO,
                                     imn06  AS  BatchNO,
                                     imn10  AS   RequestQty,
                                     imn15  AS  ToWarehouseNO,
                                     imn16  AS  ToStorageNO,
                                     imn20  AS  UnitNO,
                                    to_char(imm_file.immdate,'yyyy-MM-dd') UpdateDate,
                             imn_file.* from imn_file
                             Inner Join imm_file on imm_file.imm01=imn_file.imn01");
                sql.Append(whereSql);
                sql.OrderBy("imm_file.imm01");

                List<WmsTradeSource> Sources = db.Fetch<WmsTradeSource>(sql.SQL);
                List<WmsTradeOrderInfo> News = new List<WmsTradeOrderInfo>();
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    WmsTradeOrderInfo obj = new WmsTradeOrderInfo();
                    obj.TradeOrderNO = info.TradeOrderNO;
                    obj.SourceOrderNO = info.SourceOrderNO;
                    obj.TradeDate = info.TradeDate;
                    obj.TradeType = 5;
                    obj.TradeSources = new List<WmsTradeSource>();
                    List<WmsTradeSource> Items = Sources.FindAll(p => p.TradeOrderNO == info.TradeOrderNO);
                    foreach (WmsTradeSource item in Items)
                    {
                        obj.TradeSources.Add(item);
                    }
                    News.Add(obj);
                }

                return WmsStockTransferOrderBLL.SyncStockTransferOrder(News, out Msg);
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
        }



        /// <summary>
        /// 杂发单同步
        /// </summary>
        /// <param name="Date"></param>
        /// <param name="NO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool SyncMaterialRequisitionOrder(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;
            List<WmsTradeOrderInfo> Infos = new List<WmsTradeOrderInfo>();
            Database db = DB.D("ERPCONN");
            Sql sql = new Sql();
            try
            {

                sql = new Sql();
                sql.Append(@";Select imm_file.imm01 TradeOrderNO,to_char(imm_file.imm02,'yyyy-MM-dd') TradeDate,
                                        imm_file.imm09 Remark, 
                                        imm_file.imm14 SourceOrderNO,
                                        to_char(imm_file.immdate,'yyyy-MM-dd') UpdateDate,
                                        imm_file.*   From imm_file");

                Sql whereSql = new Sql();
                if (string.IsNullOrEmpty(Date) && string.IsNullOrEmpty(NO))
                {
                    Date = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(NO))
                {
                    //whereSql.Append("Where  sfp_file.sfp01='" + OrderNO + "'");
                    whereSql.Append("Where imm_file.imm01='" + NO + "'");

                }

                if (!string.IsNullOrEmpty(Date))
                {
                    whereSql.Append("WHere TO_CHAR(imm_file.immdate, 'yyyy-MM-dd')>='" + Date + "'");

                    DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
                    dtFormat.ShortDatePattern = "yyyy-MM-dd";
                    DateTime dt = Convert.ToDateTime(Date, dtFormat);
                    string eDate = dt.AddDays(1).ToString("yyyy-MM-dd");
                    whereSql.Append("Where  TO_CHAR(imm_file.immdate, 'yyyy-MM-dd')<='" + eDate + "'");
                }

                sql.Append(whereSql);
                sql.OrderBy("imm_file.imm01");
                Infos = db.Fetch<WmsTradeOrderInfo>(sql.ToString());
                sql = new Sql();
                sql.Append(@";Select imn01 TradeOrderNO,
                                     imn02 ErpItem,
                                     imn03 MaterialNO,
                                     imn04 FromWarehouseNO,
                                     imn05 FromStorageNO,
                                     imn06 BatchNO,
                                     imn10 RequestQty,
                                     imn15 ToWarehouseNO,
                                     imn16 ToStorageNO,
                                     imn20 UnitNO,
                                    to_char(imm_file.immdate,'yyyy-MM-dd') UpdateDate,
                             imn_file.* from imn_file
                             Inner Join imm_file on imm_file.imm01=imn_file.imn01");
                sql.Append(whereSql);
                sql.OrderBy("imm_file.imm01");
                List<WmsTradeSource> Sources = db.Fetch<WmsTradeSource>(sql);
                List<WmsTradeOrderInfo> News = new List<WmsTradeOrderInfo>();
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    WmsTradeOrderInfo obj = new WmsTradeOrderInfo();
                    obj.TradeOrderNO = info.TradeOrderNO;
                    obj.SourceOrderNO = info.SourceOrderNO;
                    obj.TradeDate = info.TradeDate;
                    obj.TradeType = 5;
                    obj.TradeSources = new List<WmsTradeSource>();
                    List<WmsTradeSource> Items = Sources.FindAll(p => p.TradeOrderNO == info.TradeOrderNO);
                    foreach (WmsTradeSource item in Items)
                    {
                        obj.TradeSources.Add(item);
                    }
                }

                return WmsMaterialRequisitionOrderBLL.SyncMaterialRequisitionOrder(News, out Msg);
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
        }


        /// <summary>
        /// 杂收单同步
        /// </summary>
        /// <param name="Date"></param>
        /// <param name="NO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool SyncMaterialReceivingOrder(string Date, string NO, out string Msg)
        {
            Msg = string.Empty;
            List<WmsTradeOrderInfo> Infos = new List<WmsTradeOrderInfo>();
            Database db = DB.D("ERPCONN");
            Sql sql = new Sql();
            try
            {

                sql = new Sql();
                sql.Append(@";Select imm_file.imm01 TradeOrderNO,to_char(imm_file.imm02,'yyyy-MM-dd') TradeDate,
                                        imm_file.imm09 Remark, 
                                        imm_file.imm14 SourceOrderNO,
                                        to_char(imm_file.immdate,'yyyy-MM-dd') UpdateDate,
                                        imm_file.*   From imm_file");

                Sql whereSql = new Sql();
                if (string.IsNullOrEmpty(Date) && string.IsNullOrEmpty(Date))
                {
                    Date = DateTime.Now.ToString("yyyy-MM-dd");
                }
                if (!string.IsNullOrEmpty(NO))
                {
                    //whereSql.Append("Where  sfp_file.sfp01='" + OrderNO + "'");
                    whereSql.Append("Where imm_file.imm01='" + NO + "'");

                }

                if (!string.IsNullOrEmpty(Date))
                {
                    whereSql.Append("WHere TO_CHAR(imm_file.immdate, 'yyyy-MM-dd')>='" + Date + "'");

                    DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
                    dtFormat.ShortDatePattern = "yyyy-MM-dd";
                    DateTime dt = Convert.ToDateTime(Date, dtFormat);
                    string eDate = dt.AddDays(1).ToString("yyyy-MM-dd");
                    whereSql.Append("Where  TO_CHAR(imm_file.immdate, 'yyyy-MM-dd')<='" + eDate + "'");
                }

                sql.Append(whereSql);
                sql.OrderBy("imm_file.imm01");
                Infos = db.Fetch<WmsTradeOrderInfo>(sql.ToString());
                sql = new Sql();
                sql.Append(@";Select imn01 TradeOrderNO,
                                     imn02 ErpItem,
                                     imn03 MaterialNO,
                                     imn04 FromWarehouseNO,
                                     imn05 FromStorageNO,
                                     imn06 BatchNO,
                                     imn10 RequestQty,
                                     imn15 ToWarehouseNO,
                                     imn16 ToStorageNO,
                                     imn20 UnitNO,
                                    to_char(imm_file.immdate,'yyyy-MM-dd') UpdateDate,
                             imn_file.* from imn_file
                             Inner Join imm_file on imm_file.imm01=imn_file.imn01");
                sql.Append(whereSql);
                sql.OrderBy("imm_file.imm01");
                List<WmsTradeSource> Sources = db.Fetch<WmsTradeSource>(sql);
                List<WmsTradeOrderInfo> News = new List<WmsTradeOrderInfo>();
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    WmsTradeOrderInfo obj = new WmsTradeOrderInfo();
                    obj.TradeOrderNO = info.TradeOrderNO;
                    obj.SourceOrderNO = info.SourceOrderNO;
                    obj.TradeDate = info.TradeDate;
                    obj.TradeType = 5;
                    obj.TradeSources = new List<WmsTradeSource>();
                    List<WmsTradeSource> Items = Sources.FindAll(p => p.TradeOrderNO == info.TradeOrderNO);
                    foreach (WmsTradeSource item in Items)
                    {
                        obj.TradeSources.Add(item);
                    }
                }

                return WmsMaterialReceivingOrderBLL.SyncMaterialReceivingOrder(News, out Msg);
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
        }


        /// <summary>
        /// 盘点单同步
        /// </summary>
        /// <param name="Date">盘点卡日期</param>
        /// <param name="NO">仓库编号</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public bool SyncStockTakeOrder(string Date, string NO, out string Msg)
        {

            Database db = DB.D("ERPCONN");
            Msg =string.Empty;
            Sql sql = new Sql();

            try
            {
                Date = "2025-06-24";

                sql = new Sql();
                sql.Append(@"   select 
                                pia01 ErpItemNO,
                                SUBSTR(pia01,1,3) TradeType,
                                TO_CHAR(pia12, 'yyyy-MM-dd') TakeDate,
                                pia19 RState,
                                pia02 MaterialNO,
                                pia03 WarehouseNO,
                                pia04 StorageNO,
                                pia05 BatchNO,
                                pia09 UnitNO,
                                pia08 InvQty
                                from pia_file
                                inner join 
                                ima_file on pia_file.pia02=ima_file.ima01");
                if(!string.IsNullOrWhiteSpace(Date))
                {
                    sql.Where("  TO_CHAR(pia_file.pia12, 'yyyy-MM-dd')='" + Date + "'");
                }
                if (!string.IsNullOrWhiteSpace(NO))
                {
                    sql.Where("  pia_file.pia03='" + NO + "'");
                }
                sql.OrderBy("pia_file.pia02,pia_file.pia01");
                List<WmsStocktakeMaterial> TakeMaterials = new List<WmsStocktakeMaterial>();
                List<WmsStocktakeOrderInfo> TakeOrders = new List<WmsStocktakeOrderInfo>();
                TakeMaterials =db.Fetch<WmsStocktakeMaterial>(sql);
                string StocktakeNO = string.Empty;
                List<WmsStocktakeMaterial> temps = new List<WmsStocktakeMaterial>();
                  temps = TakeMaterials
                        .GroupBy(p => new { p.TradeType, p.WarehouseNO }) // 按 Id 去重
                        .Select(g => g.First())
                        .ToList();
                for (int i = 0; i < temps.Count; i++)
                {
                    WmsStocktakeOrderInfo Order=new WmsStocktakeOrderInfo();
                    Order.TakeType = temps[i].TradeType;
                    Order.WarehouseNO = temps[i].WarehouseNO;
                    Order.RState ="0";
                    Order.TakeMaterials= TakeMaterials.FindAll(p=>p.TradeType == temps[i].TradeType&&p.WarehouseNO==temps[i].WarehouseNO);
                    TakeOrders.Add(Order);
                }
                WmsStocktakeOrderInfoBLL.SyncStockTakeOrder(TakeOrders, out Msg);
                return true;
            }
            catch (Exception ex)
            {
                Msg=ex.ToString();
                return false;
            }
        }

        /// <summary>
        /// 盘点单回抛
        /// </summary>
        /// <param name="NO"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool BackStockTakeOrder(string NO,out string Msg)
        { 
            Msg=string.Empty;
            try
            {

                return true;
            }
            catch (Exception ex)
            { 
                Msg = ex.ToString();
                return false;
            }
        }

















    }
}